/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controllers;

import Models.Prisoner;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Tung
 */
public class PrisonerController {

    static Connection conn = DBUtility.openConnection();

    public static ArrayList<Prisoner> GetPrisoners() {
        try {
            ArrayList<Prisoner> prisonerList = new ArrayList<Prisoner>();

            CallableStatement cs = conn.prepareCall("{call sp_GetPrisoners}", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                rs.beforeFirst();
                while (rs.next()) {
                    int _id = rs.getInt("PrisonerID");
                    prisonerList.add(new Prisoner(_id));
                }
                return prisonerList;
            }
        } catch (SQLException ex) {
            Logger.getLogger(PrisonerController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public static boolean AddPrisoner(Prisoner prisoner) {
        CallableStatement cs;
        try {
            cs = conn.prepareCall("{call sp_AddPrisoner(?,?,?,?,?)}");

            cs.setInt("CriminalID", prisoner.getRelatedCriminal().getID());
            java.sql.Date _sqlDate = new java.sql.Date(prisoner.getImprisonDate().getTime());
            cs.setDate("ImprisonDate", _sqlDate);
            if (prisoner.getReleaseDate() == null) {
                cs.setDate("ReleaseDate", null);
            } else {
                _sqlDate = new java.sql.Date(prisoner.getReleaseDate().getTime());
                cs.setDate("ReleaseDate", _sqlDate);
            }
            cs.setString("PrisonName", prisoner.getPrisonName());
            cs.setString("CellNumber", prisoner.getCellNumber());
            if (cs.executeUpdate() > 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(PrisonerController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }

    public static boolean EditPrisoner(Prisoner prisoner) {
        CallableStatement cs;
        try {
            cs = conn.prepareCall("{call sp_EditPrisoner(?,?,?,?,?,?)}");

            if (prisoner.getReleaseDate() == null) {
                cs.setDate("ReleaseDate", null);
            } else {
                java.sql.Date _sqlDate = new java.sql.Date(prisoner.getReleaseDate().getTime());
                cs.setDate("ReleaseDate", _sqlDate);
            }
            cs.setString("PrisonName", prisoner.getPrisonName());
            cs.setString("CellNumber", prisoner.getCellNumber());
            cs.setBoolean("IsReleased", prisoner.isIsReleased());
            cs.setString("ReleaseReason", prisoner.getReleaseReason());
            cs.setInt("PrisonerID", prisoner.getID());
            if (cs.executeUpdate() > 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(PrisonerController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }

    public static ArrayList<Prisoner> FilterPrisoner(HashMap parametersMap) {
        CallableStatement cs;
        Date _date = null;
        ArrayList<Prisoner> resultList = new ArrayList<Prisoner>();
        try {
            cs = conn.prepareCall("{call sp_FilterPrisoner(?,?,?,?,?,?,?,?)}", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

            _date = (Date) parametersMap.get("MinImprisonDate");
            cs.setDate("MinImprisonDate", new java.sql.Date(_date.getTime()));
            _date = (Date) parametersMap.get("MaxImprisonDate");
            cs.setDate("MaxImprisonDate", new java.sql.Date(_date.getTime()));
            _date = (Date) parametersMap.get("MinReleaseDate");
            cs.setDate("MinReleaseDate", new java.sql.Date(_date.getTime()));
            _date = (Date) parametersMap.get("MaxReleaseDate");
            cs.setDate("MaxReleaseDate", new java.sql.Date(_date.getTime()));
            cs.setString("PrisonName", parametersMap.get("PrisonName").toString());
            cs.setString("CellNumber", parametersMap.get("CellNumber").toString());
            cs.setString("IsReleased", parametersMap.get("IsReleased").toString());
            cs.setString("ReleaseReason", parametersMap.get("ReleaseReason").toString());
            
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                rs.beforeFirst();
                while (rs.next()) {
                    int _id = rs.getInt("PrisonerID");
                    resultList.add(new Prisoner(_id));
                }
                return resultList;
            }
        } catch (SQLException ex) {
            Logger.getLogger(PrisonerController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public static ArrayList<Prisoner> FilterPrisonerNoReleaseDate(HashMap parametersMap) {
        CallableStatement cs;
        Date _date = null;
        ArrayList<Prisoner> resultList = new ArrayList<Prisoner>();
        try {
            cs = conn.prepareCall("{call sp_FilterPrisonerNoReleaseDate(?,?,?,?,?,?)}", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

            _date = (Date) parametersMap.get("MinImprisonDate");
            cs.setDate("MinImprisonDate", new java.sql.Date(_date.getTime()));
            _date = (Date) parametersMap.get("MaxImprisonDate");
            cs.setDate("MaxImprisonDate", new java.sql.Date(_date.getTime()));
            cs.setString("PrisonName", parametersMap.get("PrisonName").toString());
            cs.setString("CellNumber", parametersMap.get("CellNumber").toString());
            cs.setString("IsReleased", parametersMap.get("IsReleased").toString());
            cs.setString("ReleaseReason", parametersMap.get("ReleaseReason").toString());
            
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                rs.beforeFirst();
                while (rs.next()) {
                    int _id = rs.getInt("PrisonerID");
                    resultList.add(new Prisoner(_id));
                }
                return resultList;
            }
        } catch (SQLException ex) {
            Logger.getLogger(PrisonerController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
}
